package com.zzyl.nursing.mapper;

import java.util.List;
import com.zzyl.nursing.domain.DeviceData;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zzyl.nursing.dto.MemberDataDayDto;
import com.zzyl.nursing.vo.MemberDataDayVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * 设备数据Mapper接口
 * 
 * @author ruoyi
 * @date 2024-09-22
 */
@Mapper
public interface DeviceDataMapper extends BaseMapper<DeviceData>
{
    /**
     * 查询设备数据
     * 
     * @param id 设备数据主键
     * @return 设备数据
     */
    public DeviceData selectDeviceDataById(Long id);

    /**
     * 查询设备数据列表
     * 
     * @param deviceData 设备数据
     * @return 设备数据集合
     */
    public List<DeviceData> selectDeviceDataList(DeviceData deviceData);

    /**
     * 新增设备数据
     * 
     * @param deviceData 设备数据
     * @return 结果
     */
    public int insertDeviceData(DeviceData deviceData);

    /**
     * 修改设备数据
     * 
     * @param deviceData 设备数据
     * @return 结果
     */
    public int updateDeviceData(DeviceData deviceData);

    /**
     * 删除设备数据
     * 
     * @param id 设备数据主键
     * @return 结果
     */
    public int deleteDeviceDataById(Long id);

    /**
     * 批量删除设备数据
     * 
     * @param ids 需要删除的数据主键集合
     * @return 结果
     */
    public int deleteDeviceDataByIds(Long[] ids);

    /**
     * 按小时分组，查询每个小时内的最先收集到的数据（取alarm_time最小的记录）
     */
    // 将 SQL 定义为常量，提高可读性
    String GET_HOURLY_DATA_SQL =
            "WITH RankedData AS (" +
                    "    SELECT " +
                    "        DATE_FORMAT(alarm_time, '%Y-%m-%d %H:00:00') AS date_time, " +
                    "        data_value, " +
                    "        alarm_time, " +
                    "        ROW_NUMBER() OVER(PARTITION BY DATE_FORMAT(alarm_time, '%Y-%m-%d %H:00:00') " +
                    "                          ORDER BY alarm_time) AS rn " +
                    "    FROM " +
                    "        device_data " +
                    "    WHERE " +
                    "        function_id = #{functionId} " +
                    "      AND iot_id = #{iotId} " +
                    "      AND alarm_time BETWEEN" +
                    "           CONVERT_TZ(FROM_UNIXTIME(#{startTime} / 1000), '+00:00', '+08:00')" +
                    "           AND" +
                    "           CONVERT_TZ(FROM_UNIXTIME(#{endTime} / 1000), '+00:00', '+08:00')" +
                    ") " +
                    "SELECT " +
                    "    date_time, " +
                    "    data_value " +
                    "FROM " +
                    "    RankedData " +
                    "WHERE " +
                    "    rn = 1 " +
                    "ORDER BY " +
                    "    date_time;";
    @Select(GET_HOURLY_DATA_SQL)
    List<MemberDataDayVo> selectByHour(MemberDataDayDto dto);

    /**
     * 按小时分组，查询每个小时内的最先收集到的数据（取alarm_time最小的记录）
     */
    // 将 SQL 定义为常量，提高可读性
    String GET_DAILY_DATA_SQL =
            "WITH RankedData AS (" +
                    "    SELECT " +
                    "        DATE_FORMAT(alarm_time, '%m.%d') AS date_time, " +
                    "        data_value, " +
                    "        alarm_time, " +
                    "        ROW_NUMBER() OVER(PARTITION BY DATE_FORMAT(alarm_time, '%Y-%m-%d') " +
                    "                          ORDER BY alarm_time) AS rn " +
                    "    FROM " +
                    "        device_data " +
                    "    WHERE " +
                    "        function_id = #{functionId} " +
                    "      AND iot_id = #{iotId} " +
                    "      AND alarm_time BETWEEN" +
                    "           CONVERT_TZ(FROM_UNIXTIME(#{startTime} / 1000), '+00:00', '+08:00')" +
                    "           AND" +
                    "           CONVERT_TZ(FROM_UNIXTIME(#{endTime} / 1000), '+00:00', '+08:00')" +
                    ") " +
                    "SELECT " +
                    "    date_time, " +
                    "    data_value " +
                    "FROM " +
                    "    RankedData " +
                    "WHERE " +
                    "    rn = 1 " +
                    "ORDER BY " +
                    "    date_time;";
    @Select(GET_DAILY_DATA_SQL)
    List<MemberDataDayVo> selectByDay(MemberDataDayDto dto);
}
